package com.ebupt.migu.music.common.util.excelSheet;

import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.IntStream;

import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import com.ebupt.migu.music.common.util.ExcelUtils;

/**
 * @Author zhuar
 */
public class ExcelBuilder {
    /**
     * 保存excel
     * @param book poi excel对象
     * @param excel 承载excel信息的封装对象
     */
    public static void build(Workbook book, ExcelUtils excel) {
        List<com.ebupt.migu.music.common.util.excelSheet.Sheet> sheets = excel.getSheets();
        Map<String,CellStyle> styleMap = getCellStyle(book);
        for(com.ebupt.migu.music.common.util.excelSheet.Sheet st : sheets){
            Sheet sheet = book.createSheet(st.getName());
            saveSheet(sheet, styleMap, st);
        }
    }
    /**
     * 获取Excel样式
     * @param book excel对象
     * @return 风格map
     */
    private static Map<String,CellStyle> getCellStyle(Workbook book) {
        Map<String,CellStyle> styleMap = new HashMap<String,CellStyle>();
        //一般样式
        CellStyle cellStyle = book.createCellStyle();
        cellStyle.setAlignment(HorizontalAlignment.CENTER);
        cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle.setBorderBottom(BorderStyle.THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderLeft(BorderStyle.THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyle.setBorderRight(BorderStyle.THIN);
        styleMap.put("defaultStyle", cellStyle);
        //一般样式
        CellStyle cellStyle1 = book.createCellStyle();
        cellStyle1.setAlignment(HorizontalAlignment.LEFT);
        cellStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyle1.setBorderBottom(BorderStyle.THIN);
        cellStyle1.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyle1.setBorderLeft(BorderStyle.THIN);
        cellStyle1.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyle1.setBorderRight(BorderStyle.THIN);
        styleMap.put("leftStyle", cellStyle1);
        //数字像是
        CellStyle cellStyleNum = book.createCellStyle();
        cellStyleNum.setAlignment(HorizontalAlignment.RIGHT);
        cellStyleNum.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleNum.setBorderBottom(BorderStyle.THIN);
        cellStyleNum.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyleNum.setBorderLeft(BorderStyle.THIN);
        cellStyleNum.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyleNum.setBorderRight(BorderStyle.THIN);
        cellStyleNum.setDataFormat(HSSFDataFormat
                .getBuiltinFormat("#,##0"));
        styleMap.put("numStyle", cellStyleNum);
        //收入样式
        CellStyle cellStyleFee = book.createCellStyle();
        cellStyleFee.setAlignment(HorizontalAlignment.RIGHT);
        cellStyleFee.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleFee.setBorderBottom(BorderStyle.THIN);
        cellStyleFee.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyleFee.setBorderLeft(BorderStyle.THIN);
        cellStyleFee.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyleFee.setBorderRight(BorderStyle.THIN);
        cellStyleFee.setDataFormat(HSSFDataFormat
                .getBuiltinFormat("#,##0.00"));
        styleMap.put("feeStyle", cellStyleFee);

        //百分比样式
        CellStyle cellStylePercent = book.createCellStyle();
        cellStylePercent.setAlignment(HorizontalAlignment.CENTER);
        cellStylePercent.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStylePercent.setBorderBottom(BorderStyle.THIN);
        cellStylePercent.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStylePercent.setBorderLeft(BorderStyle.THIN);
        cellStylePercent.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStylePercent.setBorderRight(BorderStyle.THIN);
        cellStylePercent.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        styleMap.put("percentStyle", cellStylePercent);

        //表头样式
        CellStyle cellStyleHeader = book.createCellStyle();
        cellStyleHeader.setAlignment(HorizontalAlignment.CENTER);
        cellStyleHeader.setVerticalAlignment(VerticalAlignment.CENTER);
        cellStyleHeader.setBorderBottom(BorderStyle.THIN);
        cellStyleHeader.setBottomBorderColor(IndexedColors.BLACK.index);
        cellStyleHeader.setBorderLeft(BorderStyle.THIN);
        cellStyleHeader.setLeftBorderColor(IndexedColors.BLACK.index);
        cellStyleHeader.setBorderRight(BorderStyle.THIN);
        //设置背景色 added by
        cellStyleHeader.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        cellStyleHeader.setFillForegroundColor(IndexedColors.AQUA.index);
        styleMap.put("headerStyle", cellStyleHeader);

        //ISV表头样式  :背景色 加粗 居中对齐
        CellStyle isvStyleHeader = book.createCellStyle();
        isvStyleHeader.setAlignment(HorizontalAlignment.CENTER);
        isvStyleHeader.setVerticalAlignment(VerticalAlignment.CENTER);
        isvStyleHeader.setBorderBottom(BorderStyle.THIN);
        isvStyleHeader.setBottomBorderColor(IndexedColors.BLACK.index);
        isvStyleHeader.setBorderLeft(BorderStyle.THIN);
        isvStyleHeader.setLeftBorderColor(IndexedColors.BLACK.index);
        isvStyleHeader.setBorderRight(BorderStyle.THIN);
        isvStyleHeader.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        isvStyleHeader.setFillForegroundColor(IndexedColors.TEAL.index);
        Font headerFont =  book.createFont(); //创建字体样式
        headerFont.setBold(true);// 字体加粗
        headerFont.setFontName("Times New Roman");  //设置字体类型
        isvStyleHeader.setFont(headerFont);    //为标题样式设置字体样式
        styleMap.put("isvHeaderStyle", isvStyleHeader);
        //ISV列头样式:背景色 加粗 左对齐
        CellStyle isvStyleColumn = book.createCellStyle();
        isvStyleColumn.setAlignment(HorizontalAlignment.LEFT);
        isvStyleColumn.setVerticalAlignment(VerticalAlignment.CENTER);
        isvStyleColumn.setBorderBottom(BorderStyle.THIN);
        isvStyleColumn.setBottomBorderColor(IndexedColors.BLACK.index);
        isvStyleColumn.setBorderLeft(BorderStyle.THIN);
        isvStyleColumn.setLeftBorderColor(IndexedColors.BLACK.index);
        isvStyleColumn.setBorderRight(BorderStyle.THIN);
        isvStyleColumn.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        isvStyleColumn.setFillForegroundColor(IndexedColors.SEA_GREEN.index);
        headerFont.setBold(true);// 字体加粗
        isvStyleColumn.setFont(headerFont);    //为标题样式设置字体样式
        styleMap.put("isvStyleColHeader", isvStyleColumn);
        //ISV一般第一列样式 左对齐
        CellStyle isvStyleCell = book.createCellStyle();
        isvStyleCell.setAlignment(HorizontalAlignment.LEFT);
        isvStyleCell.setVerticalAlignment(VerticalAlignment.CENTER);
        isvStyleCell.setBorderBottom(BorderStyle.THIN);
        isvStyleCell.setBottomBorderColor(IndexedColors.BLACK.index);
        isvStyleCell.setBorderLeft(BorderStyle.THIN);
        isvStyleCell.setLeftBorderColor(IndexedColors.BLACK.index);
        isvStyleCell.setBorderRight(BorderStyle.THIN);
        styleMap.put("isvStyleCell", isvStyleCell);
        return styleMap;
    }

    /**
     * 判断字符串是否是数值
     * @param str 字符串
     * @return 是否是数值
     */
    private static boolean isNumeric(String str) {
        if (str == null) {
            return false;
        }
        if(str.split("\\.").length > 2) {
            return false;
        }
        if(str.split("-").length > 2) {
            return false;
        }

        str = str.replace(".", "").replaceAll("-", "");
        if ("".equals(str) || str.length()==0) {
            return false;
        }
        int sz = str.length();
        for (int i = 0; i < sz; i++) {
            if (!Character.isDigit(str.charAt(i))) {
                return false;
            }
        }
        return true;
    }
    // 判断是否为整型数字
    private static boolean isNum(String s) {
        if (s.contains(".")) {
            return false;
        } else {
            return s.indexOf("-") <= 0;
        }
    }
    /**
     * 创建一行 row
     * @param sheet sheet对象
     * @param data  数据
     * @param styleMap 风格
     * @param index 行标
     */
    private static void createRow(org.apache.poi.ss.usermodel.Sheet sheet, List<Object> data, Map<String,CellStyle> styleMap, int index){
        Row row = sheet.createRow(index);
        for (int j=0;j< data.size();j++) {
            Cell cell = row.createCell(j);
            if(data.get(j)!=null){
                String value = String.valueOf(data.get(j));
                if(isNumeric(value) && j > 0 && !StringUtil.isNullOrBlank(value)){
                    if("0".equals(value.substring(0,1))){
                        cell.setCellValue(value);
                        cell.setCellStyle(styleMap.get("defaultStyle"));
                    }
                    else if(value.length()>=17){
                        cell.setCellValue(value);
                        cell.setCellStyle(styleMap.get("defaultStyle"));
                    }
                    else if(isNum(value)){
                        cell.setCellValue(Double.parseDouble(value));
                        cell.setCellStyle(styleMap.get("numStyle"));
                    }else{
                        cell.setCellValue(value);
                        cell.setCellStyle(styleMap.get("feeStyle"));
                    }
                }else{
                    if(value.contains("%")){
                        try{
                            cell.setCellValue(Double.parseDouble(value.replace("%", ""))+"%");
                            cell.setCellStyle(styleMap.get("numStyle"));
                        }catch (Exception e) {
                            if(value.contains(",")){	//超过1000%又对之前的数据格式化了的字符串型百分数，比如1，000%需要这样处理。
                                cell.setCellValue(value);
                                if(j==0){
                                    cell.setCellStyle(styleMap.get("leftStyle"));
                                }
                                else{
                                    cell.setCellStyle(styleMap.get("defaultStyle"));
                                }
                            }else{
                                cell.setCellValue(Double.parseDouble("0"));
                                cell.setCellStyle(styleMap.get("percentStyle"));
                            }
                        }

                    }else {
                        value = value.replaceAll("-", " -");
                        cell.setCellValue(value);
                        if(j==0){
                            cell.setCellStyle(styleMap.get("leftStyle"));
                        }
                        else{
                            cell.setCellStyle(styleMap.get("defaultStyle"));
                        }
                    }
                }
            }else{
                cell.setCellValue(" -");
                if(j==0){
                    cell.setCellStyle(styleMap.get("leftStyle"));
                }
                else{
                    cell.setCellStyle(styleMap.get("defaultStyle"));
                }
            }
        }
    }
    /**
     * 合并单元格
     * 逐列 逐行 进行  且 认为文本相同的需要合并
     * @param sheet sheet对象
     * @param head head数组
     */
    private static void mergeCellBySameValue(org.apache.poi.ss.usermodel.Sheet sheet, String[][] head){
        for(int i = 0; i < head[0].length;i++){
            for(int j = 0; j <head.length-1; j++){
                if(head[j][i].equals(head[j+1][i])){
                    CellRangeAddress cra=new CellRangeAddress(j, j+1, i, i);
                    sheet.addMergedRegion(cra);
                }
            }
        }
        int index=-1;
        for(int i = 0; i < head.length;i++){
            for(int j = 0; j <head[0].length-1; j++){
                if(head[i][j].equals(head[i][j+1])){
                    if(index==-1){
                        index=j;
                    }
                    if(j+2==head[0].length){
                        CellRangeAddress cra=new CellRangeAddress(i, i, index, j+1);
                        sheet.addMergedRegion(cra);
                        index=-1;
                    }
                }
                else{
                    if(index!=-1){
                        CellRangeAddress cra=new CellRangeAddress(i, i, index, j);
                        sheet.addMergedRegion(cra);
                        index=-1;
                    }
                }
            }
        }
    }
    /**
     * 合并单元格
     * 逐列 逐行 进行  且 认为文本相同的需要合并
     * @param sheet sheet对象
     * @param head head数组
     */
    private static void mergeCellBySameValue2(org.apache.poi.ss.usermodel.Sheet sheet, String[][] head){
        for(int i = 0; i < head[0].length;i++){
            for(int j = 0; j <head.length-1; j++){
                if(head[j][i].equals(head[j+1][i])){
                    CellRangeAddress cra=new CellRangeAddress(j, j+1, i, i);
                    sheet.addMergedRegion(cra);
                }
            }
        }
        for(int i = 0; i < head.length;i++){
            for(int j = 0; j <head[0].length-1; j++){
                if(head[i][j].equals(head[i][j+1])){
                    CellRangeAddress cra=new CellRangeAddress(i, i, j, j+1);
                    sheet.addMergedRegion(cra);
                }
            }
        }
    }
    /**
     * 保存单个sheet
     * @param sheet sheet对象
     * @param styleMap 风格
     * @param sheetBean sheet数据
     */
    private static void saveSheet(Sheet sheet, Map<String,CellStyle>  styleMap, com.ebupt.migu.music.common.util.excelSheet.Sheet sheetBean) {
        String[][] head = sheetBean.getHead();
        List<List<Object>> data = sheetBean.getData();
        for(int i = 0; i < head.length;i++){
            Row row = sheet.createRow(i);
            for (int j=0; j< head[i].length; j++) {
                //创建Sheet表头
                Cell cell = row.createCell(j);
                cell.setCellValue(head[i][j]);
                //添加表头样式
                cell.setCellStyle(styleMap.get("headerStyle"));
            }
        }
        mergeCellBySameValue(sheet,head); // 合并表头
        //插入数据
        IntStream.range(0, data.size()).forEach(i -> {
            List<Object> rowData = data.get(i);
            createRow(sheet, rowData, styleMap, i + head.length);
        });
        // 宽
        IntStream.range(0, head.length).forEach(i -> sheet.setColumnWidth(i, 5500));
    }

}